package fy.uap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.LogFactory;

import com.seeyon.ctp.util.UUIDLong;

import fy.config.FyConfig;
import fy.uap.bean.MergerFinanceSupplierBean;
import fy.util.DBUtil;
import fy.util.MyUtil;

public class MergerFinanceSupplierDao {

	private static org.apache.commons.logging.Log log = LogFactory
			.getLog(MergerFinanceSupplierDao.class);

	private static final String RENAMESQL = "update fy_supplier_vendor set cvenname = ? , cvencode = ? where cvencode = ? and (dr='0' or dr is null) and cvendefine1 = 'financial'";

	private static final String MERGERSQL = "insert into fy_supplier_vendor_invclass ( pk_veninv, pk_corp, pk_invclass, cvendorclass, denddate, dvendevdate, dlastdate, pk_vendor, creater ) select ?, ?, ?, ?, ?, ?, ?, (select pk_vendor from fy_supplier_vendor where cvencode = ? and (dr = '0' or dr is null) and cvendefine1 = 'financial'), ?  from dual where not exists( select * from fy_supplier_vendor_invclass f where f.pk_vendor=(select pk_vendor from  fy_supplier_vendor where cvencode = ? and (dr='0' or dr is null) and cvendefine1 = 'financial') and trim(f.pk_corp) = ? and f.pk_invclass=? and (f.dr='0' or f.dr is null))";

	private static final String STOPOLDINCLASS = "update fy_supplier_vendor_invclass set denddate = to_char( sysdate, 'yyyy-mm-dd') where pk_vendor=(select v.pk_vendor from  fy_supplier_vendor v where v.cvencode = ? and (v.dr='0' or v.dr is null)) and (dr='0' or dr is null) and denddate is null";

	private static final String STOPSUPPLIERSQL = "update fy_supplier_vendor set denddate = to_char( sysdate, 'yyyy-mm-dd') where cvencode=? and (dr='0' or dr is null)";

	private static final String LOGSQL = "insert into fy_supplier_vendor_history(pk_venhis, pk_vendor, pk_corp, pk_invclass, changetype, changememo, changevouch, changecode, changer, ts, changedate, creater) select ?, (select v.pk_vendor from fy_supplier_vendor v where v.cvencode = ? and (v.dr='0' or v.dr is null) and v.cvendefine1 = 'financial'), ?, ?, ?, ?, ?, ?, ?, ?, ?, ? from dual";

	private static final String QUERYINCLASS = "select pk_veninv, pk_corp, pk_invclass, cvendorclass, denddate, dvendevdate, dlastdate, pk_vendor, creater from fy_supplier_vendor_invclass vi where vi.pk_vendor = (select v.pk_vendor from fy_supplier_vendor v where v.cvencode =? and (v.dr = '0' or v.dr is null) and v.cvendefine1 = 'financial')";

	private static final String ERPSTOPSQL = "update apps.FYG_PO_UAP_VENDORS@"
			+ FyConfig.getERP_DB_CONFIG().getDblink()
			+ " i set i.flag='N' where i.VENDORS_NUM = ? and i.UAPLB = 'NONE'  and i.ZT<>'客户'  ";

	private static final String ERPDELETESQL = "delete from apps.FYG_PO_UAP_VENDORS@"
			+ FyConfig.getERP_DB_CONFIG().getDblink() + " i where i.VENDORS_NUM = ? and i.UAPLB = 'NONE' and i.ZT<>'客户' ";


	private static final String ERPINSERTSQL = "insert into apps.FYG_PO_UAP_VENDORS@"
			+ FyConfig.getERP_DB_CONFIG().getDblink()
			+ "(VENDORS_NUM, VENDORS_NAME, SYB, UAPLB, ZT, disable_date,flag) select * from (select v.cvencode cvencode,  v.cvenname cvenname,  c.unitname unitname,  i.cinvclasscode cinvclasscode,  decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),-1,'停用',decode(vi.cvendorclass,'1','合格',  '2',  '潜在',  '3',  '黑名单',  vi.cvendorclass)) zt,  to_date(vi.denddate, 'YYYY-MM-DD') disable_date,  decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),-1,'N',decode(vi.cvendorclass,  '3',  'N',  'Y')) flag    from fy_supplier_vendor_invclass vi    left join fy_supplier_vendor v on vi.pk_vendor = v.pk_vendor    left join bd_corp c on vi.pk_corp = c.pk_corp    left join fy_supplier_invclass i on vi.pk_invclass = i.pk_invclass where v.cvencode=? and i.cinvclasscode='NONE'  and (vi.dr is null or vi.dr = 0) ) t where not exists ( select *    from apps.FYG_PO_UAP_VENDORS@"
			+ FyConfig.getERP_DB_CONFIG().getDblink()
			+ " e where t.cvencode = e.VENDORS_NUM and e.SYB = t.unitname and e.UAPLB=t.cinvclasscode and t.zt = e.zt and (t.disable_date is null and e.disable_date is null or t.disable_date = e.disable_date) ) ";

	private List<MergerFinanceSupplierBean> beans;

	// private List<FinanceSupplierBean> financeSupplierBeans;

	private String number;

	// private String applicationTime;

	private String memberCode;

	private final static String uapUserID = "0001AA10000000009KX7";

	public void setBeans(List<MergerFinanceSupplierBean> beans) {
		this.beans = beans;
	}

	public void setNumber(String number) {
		this.number = number;
	}

	public void setApplicationTime(String applicationTime) {
		// this.applicationTime = applicationTime;
	}

	public void setMemberCode(String memberCode) {
		this.memberCode = memberCode;
	}

	public boolean save() {
		boolean success = false;

		Connection conn = null;
		PreparedStatement renamedPst = null;
		PreparedStatement mergerPst = null;
		PreparedStatement stopInclassPst = null;
		PreparedStatement stopSupplierPst = null;

		PreparedStatement erpStopPst = null;
		PreparedStatement erpDeletePst = null;
		PreparedStatement erpInsertPst = null;

		PreparedStatement queryInclassPst = null;
		PreparedStatement logPst = null;
		ResultSet rs = null;

		try {

			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());
			conn.setAutoCommit(false);

			renamedPst = conn.prepareStatement(RENAMESQL);
			mergerPst = conn.prepareStatement(MERGERSQL);
			stopInclassPst = conn.prepareStatement(STOPOLDINCLASS);
			stopSupplierPst = conn.prepareStatement(STOPSUPPLIERSQL);
			logPst = conn.prepareStatement(LOGSQL);

			erpStopPst = conn.prepareStatement(ERPSTOPSQL);
			erpDeletePst = conn.prepareStatement(ERPDELETESQL);
			erpInsertPst = conn.prepareStatement(ERPINSERTSQL);

			queryInclassPst = conn.prepareStatement(QUERYINCLASS);

			boolean rename = false;
			boolean merger = false;

			List<String> tempCodeList = new ArrayList<String>();
			if (beans != null && beans.size() > 0) {
				int state = 1;
				for (int i = 0, size = beans.size(); i < size; i++) {
					MergerFinanceSupplierBean bean = beans.get(i);
					if (bean.getOperate() == MergerFinanceSupplierBean.RENAMED) {
						// 更名
						renamedPst.setString(1, bean.getNewName());
						renamedPst.setString(
								2,
								MyUtil.strIsNotNull(bean.getNewCode()) ? bean
										.getNewCode() : bean.getOldCode());
						renamedPst.setString(3, bean.getOldCode());
						renamedPst.addBatch();

						logPst.setString(1, String
								.valueOf(UUIDLong
										.longUUID()));
						logPst.setString(2, bean.getOldCode());
						logPst.setString(3, bean.getPk_corp());
						logPst.setString(4, "");
						logPst.setString(5, "更名");
						logPst.setString(6, new StringBuilder().append("原供应商【")
								.append(bean.getOldName()).append("】更名为【")
								.append(bean.getNewName()).append("】;备注：")
								.append(bean.getRemark()).toString());
						logPst.setString(7, "OA供应商更名/合并批准表");
						logPst.setString(8, number);
						logPst.setString(9, uapUserID);
						logPst.setString(10, MyUtil.getNowStr());
						logPst.setString(11, MyUtil.getTodayDateStr());
						logPst.setString(12, memberCode);
						logPst.addBatch();

						erpDeletePst.setString(1, bean.getOldCode());
						erpDeletePst.addBatch();

						if (tempCodeList.indexOf(bean.getNewCode()) == -1) {
							erpInsertPst.setString(1, bean.getNewCode());
							erpInsertPst.addBatch();
							tempCodeList.add(bean.getNewCode());
						}
						rename = true;

					} else if (bean.getOperate() == MergerFinanceSupplierBean.MERGER) {
						// 合并
						merger = true;
						queryInclassPst.setString(1, bean.getOldCode());
						rs = queryInclassPst.executeQuery();
						String note = new StringBuilder().append("供应商【")
								.append(bean.getOldName()).append("】合并到【")
								.append(bean.getNewName()).append("】;备注：")
								.append(bean.getRemark()).toString();
						while (rs.next()) {

							mergerPst
									.setString(
											1,
											String.valueOf(UUIDLong
													.longUUID()));
							mergerPst.setString(2, rs.getString(2));
							mergerPst.setString(3, rs.getString(3));
							mergerPst.setString(4, rs.getString(4));
							mergerPst.setString(5, rs.getString(5));
							mergerPst.setString(6, rs.getString(6));
							mergerPst.setString(7, rs.getString(7));
							mergerPst.setString(8, bean.getNewCode());
							mergerPst.setString(9, memberCode);
							mergerPst.setString(10, bean.getNewCode());
							mergerPst.setString(11, rs.getString(2));
							mergerPst.setString(12, rs.getString(3));
							mergerPst.addBatch();

							logPst.setString(
									1,
									String.valueOf(UUIDLong
											.longUUID()));
							logPst.setString(2, rs.getString(8));
							logPst.setString(3, rs.getString(2));
							logPst.setString(4, rs.getString(3));
							logPst.setString(5, "合并");
							logPst.setString(6, note);
							logPst.setString(7, "OA供应商更名/合并批准表");
							logPst.setString(8, number);
							logPst.setString(9, uapUserID);
							logPst.setString(10, MyUtil.getNowStr());
							logPst.setString(11, MyUtil.getTodayDateStr());
							logPst.setString(12, memberCode);

							logPst.addBatch();
						}

						queryInclassPst.clearParameters();

						stopInclassPst.setString(1, bean.getOldCode());
						stopInclassPst.addBatch();

						stopSupplierPst.setString(1, bean.getOldCode());
						stopSupplierPst.addBatch();

						// ERP停用原供应商
						erpStopPst.setString(1, bean.getOldCode());
						erpStopPst.addBatch();

						// ERP删除目标供应商
						erpDeletePst.setString(1, bean.getNewCode());
						erpDeletePst.addBatch();

						// ERP　从ＵＡＰ读取数据，插入目标供应商
						if (tempCodeList.indexOf(bean.getNewCode()) == -1) {
							erpInsertPst.setString(1, bean.getNewCode());
							erpInsertPst.addBatch();
							tempCodeList.add(bean.getNewCode());
						}
					} else {
						state = 0;
						break;
					}

				}
				if (state == 1) {

					logPst.executeBatch();

					if (rename) {
						renamedPst.executeBatch();
					}

					if (merger) {
						mergerPst.executeBatch();
						stopInclassPst.executeBatch();
						stopSupplierPst.executeBatch();
						erpStopPst.executeBatch();
					}

					erpDeletePst.executeBatch();
					erpInsertPst.executeBatch();
				}

				conn.commit();
				success = true;
			}
			if (!success) {
				conn.rollback();
			}

		} catch (Exception e) {
			success = false;
			DBUtil.rollback(conn);
			e.printStackTrace();
			log.error("《供应商编码新增申请单》导入UPA异常，单据编号：" + number, e);
		} finally {
			DBUtil.setAutoCommit(conn, true);

			DBUtil.close(renamedPst);

			DBUtil.close(queryInclassPst);

			DBUtil.close(stopInclassPst);
			DBUtil.close(stopSupplierPst);

			DBUtil.close(logPst);

			DBUtil.close(erpStopPst);
			DBUtil.close(erpDeletePst);
			DBUtil.close(erpInsertPst);

			DBUtil.close(rs, mergerPst, conn);
		}

		return success;
	}

	public boolean writeToErp() {
		boolean success = true;
		// Connection conn = null;
		// PreparedStatement pst = null;
		// try {
		//
		// conn = DBUtil.connect(ERP_OAConstant.ERPDriverClassName,
		// ERP_OAConstant.ERPURL, ERP_OAConstant.ERPUserName,
		// ERP_OAConstant.ERPPassword);
		//
		// conn.setAutoCommit(false);
		//
		// pst = conn
		// .prepareStatement("insert into apps.FYG_PO_UAP_VENDORS@from_erp (VENDORS_NUM, VENDORS_NAME, SYB, UAPLB, ZT) select v.cvencode,v.cvenname,c.unitname, 'NONE','99' from fy_supplier_vendor_invclass vi left join fy_supplier_vendor v on vi.pk_vendor = v.pk_vendor left join bd_corp c on vi.pk_corp = c.pk_corp where v.cvendefine1 = 'financial' and v.cvencode = ?");
		// // insert into apps.FYG_PO_UAP_VENDORS (VENDORS_NUM 供应商编号,
		// // VENDORS_NAME 供应商名称, SYB 事业部, UAPLB UAP类别, ZT 状态) select ?, ?,
		// // ?,
		// // ?, ?, ?, ?, ?, ? from dual where not exists ( select * from
		// // apps.FYG_PO_UAP_VENDORS f where f.VENDORS_NUM=? and f.SY=?
		// // and
		// // f.UAPLB=?)");
		// FinanceSupplierBean range = null;
		// for (int i = 0, size = financeSupplierBeans.size(); i < size; i++) {
		// range = financeSupplierBeans.get(i);
		// pst.setString(1, range.getVendors_num());
		// pst.setString(2, range.getVendors_name());
		// pst.setString(3, range.getSyb());
		// pst.setString(4, "NONE");
		// pst.setString(5, "99");
		// pst.setString(6, range.getVendors_num());
		// pst.setString(7, range.getSyb());
		//
		// pst.addBatch();
		// range = null;
		// }
		// pst.executeBatch();
		//
		// conn.commit();
		//
		// success = true;
		//
		// } catch (Exception e) {
		// DBUtil.rollback(conn);
		// DBUtil.setAutoCommit(conn, true);
		// e.printStackTrace();
		// log.error("《供应商编码新增申请单》写入ERP数据库异常,单据编号： " + number, e);
		// success = false;
		// } finally {
		// DBUtil.setAutoCommit(conn, true);
		// DBUtil.close(pst, conn);
		// }
		return success;
	}

	// private class SupplyTypeBean {
	// private String pk_veninv;
	// private String pk_corp;
	// private String pk_invclass;
	// private String cvendorclass;
	// private String denddate;
	// private String dvendevdate;
	// private String dlastdate;
	// private String pk_vendor;
	//
	// public String getPk_veninv() {
	// return pk_veninv;
	// }
	//
	// public void setPk_veninv(String pk_veninv) {
	// this.pk_veninv = pk_veninv;
	// }
	//
	// public String getPk_corp() {
	// return pk_corp;
	// }
	//
	// public void setPk_corp(String pk_corp) {
	// this.pk_corp = pk_corp;
	// }
	//
	// public String getPk_invclass() {
	// return pk_invclass;
	// }
	//
	// public void setPk_invclass(String pk_invclass) {
	// this.pk_invclass = pk_invclass;
	// }
	//
	// public String getCvendorclass() {
	// return cvendorclass;
	// }
	//
	// public void setCvendorclass(String cvendorclass) {
	// this.cvendorclass = cvendorclass;
	// }
	//
	// public String getDenddate() {
	// return denddate;
	// }
	//
	// public void setDenddate(String denddate) {
	// this.denddate = denddate;
	// }
	//
	// public String getDvendevdate() {
	// return dvendevdate;
	// }
	//
	// public void setDvendevdate(String dvendevdate) {
	// this.dvendevdate = dvendevdate;
	// }
	//
	// public String getDlastdate() {
	// return dlastdate;
	// }
	//
	// public void setDlastdate(String dlastdate) {
	// this.dlastdate = dlastdate;
	// }
	//
	// public String getPk_vendor() {
	// return pk_vendor;
	// }
	//
	// public void setPk_vendor(String pk_vendor) {
	// this.pk_vendor = pk_vendor;
	// }
	// }
}

class FinanceSupplierBean {
	private String vendors_num;
	private String vendors_name;
	private String syb;

	public String getVendors_num() {
		return vendors_num;
	}

	public void setVendors_num(String vendors_num) {
		this.vendors_num = vendors_num;
	}

	public String getVendors_name() {
		return vendors_name;
	}

	public void setVendors_name(String vendors_name) {
		this.vendors_name = vendors_name;
	}

	public String getSyb() {
		return syb;
	}

	public void setSyb(String syb) {
		this.syb = syb;
	}
}
